﻿/******************************************************************
** 文 件 名: Bu_AuditTable_DAL.cs
** Copyright (c) 2010-2012 Honestysoft (GuangZhou) Co.,LTD
** 创 建 人: 此代码由生成器自动生成
** 创建日期: 2012-3-21 23:53:16
** 描    述: 实现公共部分的数据访问封装，提供内部审核作业表的数据访问类
** 版    本: 2.0.0.0
** 修改描述:
** 修 改 人:
** 修改日期:
******************************************************************/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using Honestysoft.BaseObject;
using Honestysoft.Financing.Model;

namespace Honestysoft.Financing.DataAccess
{
	/// <summary>
    /// Bu_AuditTable数据访问类
    /// </summary>
	public partial class Bu_AuditTable_DAL : BaseDataAccess<Bu_AuditTable>
    {		
		/// <summary>
        /// 构造函数，使用指定的连接字符串
        /// </summary>
        /// <param name="connectionStringName">连接字符串名称</param>
        public Bu_AuditTable_DAL(string connectionStringName)
            : base(connectionStringName)
        {

        }

        public DataTable GetBatchProject()
        {
            string @sql = @"Select A.BatchID,B.ID ProjectID,B.Name ProjectName,Count(D.ContractID) ContractCount
                From Bu_BatchContract A
                Inner Join BD_Contract C On A.ContractID=C.ID
                Inner Join BD_Project B On C.ProjectID=B.ID
                Inner Join
                (
                Select A.BatchID,B.ProjectID,A.ContractID
                From Bu_BatchContract A
                Inner Join BD_Contract B On A.ContractID=B.ID) D
                On B.ID=D.ProjectID And A.BatchID=D.BatchID
                Group by A.BatchID,B.ID,B.Name ";
            return ExecuteDataTable(sql);
        }

        public DataTable GetBatchProject(string batchId)
        {
            string @sql = @"Select A.BatchID,B.ID ProjectID,B.Name ProjectName,B.SortCode,Count(D.ContractID) ContractCount
                From Bu_BatchContract A
                Inner Join BD_Contract C On A.ContractID=C.ID
                Inner Join BD_Project B On C.ProjectID=B.ID
                Inner Join
                (
                Select A.BatchID,B.ProjectID,A.ContractID
                From Bu_BatchContract A
                Inner Join BD_Contract B On A.ContractID=B.ID) D
                On B.ID=D.ProjectID And A.BatchID=D.BatchID
                Where A.BatchID='" +batchId+@"'
                Group by A.BatchID,B.ID,B.Name,B.SortCode Order by B.SortCode";
            return ExecuteDataTable(sql);
        }

        public DataTable GetBatchContract(string batchId, string projectId)
        {
            string @sql = @"Select A.BatchID,A.ContractID,B.Name ContractName,B.Code,Count(C.ID) TableCount
                From Bu_BatchContract A
                Inner Join BD_Contract B On A.ContractID=B.ID
                Left Join
                (Select ID,BatchID,ContractID From Bu_AuditTable) C
                On A.ContractID=C.ContractID
                And A.BatchID=C.ID
                Where A.BatchID='" + batchId + @"' And B.ProjectID='" + projectId + @"'
                Group by A.BatchID,A.ContractID,B.Name,B.Code Order by B.Code";
            return ExecuteDataTable(sql);
        }

        public bool CreateAuditTable(string userId, string batchId, string contractId, string code, decimal curAmount, decimal conPerAmount)
        {
            DbCommand command = GetStoredProcCommand("Proc_CreateAuditTable");
            DbParameter pUserID = CreateParameter("UserID");
            pUserID.DbType = DbType.String;
            pUserID.Value = userId;
            DbParameter pBatchID = CreateParameter("BatchID");
            pBatchID.DbType = DbType.String;
            pBatchID.Value = batchId;
            DbParameter pContractID = CreateParameter("ContractID");
            pContractID.DbType = DbType.String;
            pContractID.Value = contractId;
            DbParameter pCode = CreateParameter("Code");
            pCode.DbType = DbType.String;
            pCode.Value = code;
            DbParameter pCurAmount = CreateParameter("CurAmount");
            pCurAmount.DbType = DbType.Decimal;
            pCurAmount.Value = curAmount;
            DbParameter pConPerAmount = CreateParameter("ConPerAmount");
            pConPerAmount.DbType = DbType.Decimal;
            pConPerAmount.Value = conPerAmount;

            command.Parameters.Add(pUserID);
            command.Parameters.Add(pBatchID);
            command.Parameters.Add(pContractID);
            command.Parameters.Add(pCode);
            command.Parameters.Add(pCurAmount);
            command.Parameters.Add(pConPerAmount);

            ExecuteNonQuery(command);

            return true;
        }

        public string GetNextAuditCode(string contractId)
        {
            DbCommand command = GetStoredProcCommand("Proc_GetNextAuditCode");
            DbParameter pContractID = CreateParameter("ContractID");
            pContractID.DbType = DbType.String;
            pContractID.Value = contractId;
            command.Parameters.Add(pContractID);
            return ExecuteScalar(command).ToString();
        }

        /// <summary>
        /// 更新初审数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="amount"></param>
        /// <param name="tAmount_C_A"></param>
        /// <param name="amountHT"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int UpdateAuditTable_F(string id, decimal docRecAmount, decimal amount, decimal tAmount_C_A, decimal amountHT, DbTransaction transaction)
        { 
            DbParameter pID=GetDbParameter("ID",id);
            DbParameter pAmount_DocRec_F = GetDbParameter("Amount_DocRec_F", docRecAmount);
            DbParameter pAmount_DocRec_S = GetDbParameter("Amount_DocRec_S", docRecAmount);
            DbParameter pAmount_F=GetDbParameter("Amount_F",amount);           
            DbParameter pAmount_S=GetDbParameter("Amount_S",amount);           
            DbParameter pTAmount_C_A_F=GetDbParameter("TAmount_C_A_F",tAmount_C_A);           
            DbParameter pTAmount_C_A_S=GetDbParameter("TAmount_C_A_S",tAmount_C_A);
            DbParameter pAmount_HT_F = GetDbParameter("Amount_C_F", amountHT);
            DbParameter pAmount_HT_S = GetDbParameter("Amount_C_S", amountHT);
            DbParameter pIsInputAmount_F = GetDbParameter("IsInputAmount_F", true);
            DbParameter pIsInputAmount_C_F = GetDbParameter("IsInputAmount_C_F", true);
            DbParameter pIsInputTAmount_C_A_F = GetDbParameter("IsInputTAmount_C_A_F", true);
            DbParameter pIsInputAmount_DocRec_F = GetDbParameter("IsInputAmount_DocRec_F", true);
            string sql = string.Format(@"Update Bu_AuditTable Set 
                    Amount_F={0},Amount_S={1},
                    TAmount_C_A_F={2},TAmount_C_A_S={3},
                    Amount_C_F={4},Amount_C_S={5}, 
                    IsInputAmount_F={6},IsInputTAmount_C_A_F={7},IsInputAmount_C_F={8},Amount_DocRec_F={9},Amount_DocRec_S={10},IsInputAmount_DocRec_F={11}
                    Where ID={12}",
                pAmount_F.ParameterName, pAmount_S.ParameterName, 
                pTAmount_C_A_F.ParameterName, pTAmount_C_A_S.ParameterName, 
                pAmount_HT_F.ParameterName,pAmount_HT_S.ParameterName,
                pIsInputAmount_F.ParameterName,pIsInputTAmount_C_A_F.ParameterName,pIsInputAmount_C_F.ParameterName,
                pAmount_DocRec_F.ParameterName,pAmount_DocRec_S.ParameterName,pIsInputAmount_DocRec_F.ParameterName,
                pID.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pID);
            command.Parameters.Add(pAmount_F);
            command.Parameters.Add(pAmount_S);
            command.Parameters.Add(pTAmount_C_A_F);
            command.Parameters.Add(pTAmount_C_A_S);
            command.Parameters.Add(pAmount_HT_F);
            command.Parameters.Add(pAmount_HT_S);
            command.Parameters.Add(pIsInputAmount_F);
            command.Parameters.Add(pIsInputTAmount_C_A_F);
            command.Parameters.Add(pIsInputAmount_C_F);
            command.Parameters.Add(pAmount_DocRec_F);
            command.Parameters.Add(pAmount_DocRec_S);
            command.Parameters.Add(pIsInputAmount_DocRec_F);
            return ExecuteNonQuery(command, transaction);
        }
        /// <summary>
        /// 更新复核数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="amount"></param>
        /// <param name="tAmount_C_A"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int UpdateAuditTable_S(string id, decimal docRecAmount, decimal amount, decimal tAmount_C_A, decimal amountHT, DbTransaction transaction)
        {
            DbParameter pID = GetDbParameter("ID", id);
            DbParameter pAmount_S = GetDbParameter("Amount_S", amount);
            DbParameter pAmount_DocRec_S = GetDbParameter("Amount_DocRec_S", docRecAmount);
            DbParameter pTAmount_C_A_S = GetDbParameter("TAmount_C_A_S", tAmount_C_A);
            DbParameter pAmount_HT_S = GetDbParameter("Amount_C_S", amountHT);
            DbParameter pIsInputAmount_S = GetDbParameter("IsInputAmount_S", true);
            DbParameter pIsInputAmount_C_S = GetDbParameter("IsInputAmount_C_S", true);
            DbParameter pIsInputTAmount_C_A_S = GetDbParameter("IsInputTAmount_C_A_S", true);
            DbParameter pIsInputAmount_DocRec_S = GetDbParameter("IsInputAmount_DocRec_S", true);
            string sql = string.Format(@"Update Bu_AuditTable Set 
                    Amount_S={0},
                    TAmount_C_A_S={1},
                    Amount_C_S={2}, 
                    IsInputAmount_S={3},IsInputTAmount_C_A_S={4},IsInputAmount_C_S={5},Amount_DocRec_S={6},IsInputAmount_DocRec_S={7}
                    Where ID={8}",
                pAmount_S.ParameterName,
                pTAmount_C_A_S.ParameterName,
                pAmount_HT_S.ParameterName,
                pIsInputAmount_S.ParameterName, pIsInputTAmount_C_A_S.ParameterName, pIsInputAmount_C_S.ParameterName,
                pAmount_DocRec_S.ParameterName,pIsInputAmount_DocRec_S.ParameterName,
                pID.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pID);
            command.Parameters.Add(pAmount_S);
            command.Parameters.Add(pTAmount_C_A_S);
            command.Parameters.Add(pAmount_HT_S);
            command.Parameters.Add(pIsInputAmount_S);
            command.Parameters.Add(pIsInputTAmount_C_A_S);
            command.Parameters.Add(pIsInputAmount_C_S);
            command.Parameters.Add(pAmount_DocRec_S);
            command.Parameters.Add(pIsInputAmount_DocRec_S);
            return ExecuteNonQuery(command, transaction);
        }


        public int UpdateAuditTable(string id, decimal docRecAmount, decimal amount, decimal tAmount_C_A, decimal amountHT, DbTransaction transaction)
        {
            DbParameter pID = GetDbParameter("ID", id);
            DbParameter pAmount_S = GetDbParameter("Amount_S", amount);
            DbParameter pAmount_DocRec_S = GetDbParameter("Amount_DocRec_S", docRecAmount);
            DbParameter pTAmount_C_A_S = GetDbParameter("TAmount_C_A_S", tAmount_C_A);
            DbParameter pAmount_HT_S = GetDbParameter("Amount_C_S", amountHT);
            string sql = string.Format(@"Update Bu_AuditTable Set 
                    Amount_S={0},
                    TAmount_C_A_S={1},
                    Amount_C_S={2},
                    Amount_DocRec_S={3}
                    Where ID={4}",
                pAmount_S.ParameterName,
                pTAmount_C_A_S.ParameterName,
                pAmount_HT_S.ParameterName,
                pAmount_DocRec_S.ParameterName,
                pID.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pID);
            command.Parameters.Add(pAmount_S);
            command.Parameters.Add(pTAmount_C_A_S);
            command.Parameters.Add(pAmount_HT_S);
            command.Parameters.Add(pAmount_DocRec_S);
            return ExecuteNonQuery(command, transaction);
        }

        /// <summary>
        /// 刷新内部审核作业表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int RefreshAuditTable(string id, DbTransaction transaction)
        {
            DbParameter pID = CreateParameter("id");
            pID.DbType = DbType.String;
            pID.Value = id;
            DbCommand command = GetStoredProcCommand("Proc_RefreshAT");
            command.Parameters.Add(pID);
            return ExecuteNonQuery(command, transaction);
        }
        /// <summary>
        /// 计算内部审核作业表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int ComputeAuditTable(string id, DbTransaction transaction)
        {
            DbParameter pID = CreateParameter("id");
            pID.DbType = DbType.String;
            pID.Value = id;
            DbCommand command = GetStoredProcCommand("Proc_ComputeAT");
            command.Parameters.Add(pID);
            return ExecuteNonQuery(command, transaction);
        }

        public DataTable GetLastBatchAuditTable()
        {
            string sql = @"Select * From Bu_AuditTable Where BatchNo=(Select max(BatchNo) From Bu_AuditBatch) Order by Code";
            DbCommand command = GetDbCommand(sql);
            return ExecuteDataTable(command);
        }

        /// <summary>
        /// 得到放款、拨付记录
        /// </summary>
        /// <param name="batchNo"></param>
        /// <param name="projectSN"></param>
        /// <param name="projectName"></param>
        /// <param name="contractName"></param>
        /// <param name="code"></param>
        /// <param name="isGive"></param>
        /// <param name="isBF"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentPageIndex"></param>
        /// <param name="pageCount"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataTable GetBFGive(int? batchNo, string projectSN,string projectName,string contractName,string code,bool? isGive,bool? isBF,int pageSize,int currentPageIndex,out int pageCount,out int recordCount)
        {
            DbParameter pBatchNo=CreateParameter("BatchNo");
            if(batchNo.HasValue)
            {
            pBatchNo.DbType = DbType.Int32;
            pBatchNo.Value=batchNo.Value;
            }
            DbParameter pProjectSN=CreateParameter("SN");
            if(!string.IsNullOrEmpty(projectSN))
            {
            pProjectSN.DbType = DbType.String;
                pProjectSN.Value = projectSN;
            }
            DbParameter pProjectName=CreateParameter("ProjectName");
            if(!string.IsNullOrEmpty(projectName))
            {
            pProjectName.DbType = DbType.String;
                pProjectName.Value = projectName;
            }
            DbParameter pContractName=CreateParameter("ContractName");
            if(!string.IsNullOrEmpty(contractName))
            {
            pContractName.DbType = DbType.String;
                pContractName.Value = contractName;
            }
            DbParameter pCode=CreateParameter("Code");
            if(!string.IsNullOrEmpty(code))
            {
            pCode.DbType = DbType.String;
                pCode.Value = code;
            }
            DbParameter pIsBF=CreateParameter("IsBF");
            if(isBF.HasValue)
            {
                pIsBF.DbType = DbType.Boolean;
                pIsBF.Value=isBF.Value;
            }
            DbParameter pIsGive=CreateParameter("IsGive");
            if(isGive.HasValue)
            {
                pIsGive.DbType = DbType.Boolean;
                pIsGive.Value=isGive.Value;
            }
            string sql = string.Format(@"Select A.ID,B.SN ProjectSN,A.BatchNo,A.ProjectName,A.ContractName,A.Code,A.Amount_DocRec_S,A.OwnerName,A.ContractorName,
                A.Amount_S,A.IsGive,A.GiveAmount,A.GiveDate,A.IsBF,A.BFAmount,A.BFDate
                From Bu_AuditTable A Inner Join BD_Project B
                On A.ProjectID=B.ID
                Where 1=1 {0} {1} {2} {3} {4} {5} {6}  ", batchNo.HasValue ? string.Format(" And A.BatchNo={0} ", pBatchNo.ParameterName) : "",
             string.IsNullOrEmpty(projectSN) ? "" : string.Format(" And B.SN LIKE '%'+{0}+ '%' ", pProjectSN.ParameterName),
             string.IsNullOrEmpty(projectName) ? "" : string.Format(" And A.ProjectName LIKE '%'+{0}+ '%' ", pProjectName.ParameterName),
             string.IsNullOrEmpty(contractName) ? "" : string.Format(" And A.ContractName LIKE '%'+{0}+ '%' ", pContractName.ParameterName),
             string.IsNullOrEmpty(code) ? "" : string.Format(" And A.Code LIKE '%'+{0}+ '%' ", pCode.ParameterName),
             isBF.HasValue ? string.Format(" And A.IsBF={0}", pIsBF.ParameterName) : "",
             isGive.HasValue ? string.Format(" And A.IsGive={0}", pIsGive.ParameterName) : "");
            DbCommand command = GetDbCommand(sql);
            if (batchNo.HasValue)
            {
                command.Parameters.Add(pBatchNo);
            }
            if (!string.IsNullOrEmpty(projectSN))
            {
                command.Parameters.Add(pProjectSN);
            }
            if (!string.IsNullOrEmpty(projectName))
            {
                command.Parameters.Add(pProjectName);
            }
            if (!string.IsNullOrEmpty(contractName))
            {
                command.Parameters.Add(pContractName);
            }
            if (!string.IsNullOrEmpty(code))
            {
                command.Parameters.Add(pCode);
            }
            if (isBF.HasValue)
            {
                command.Parameters.Add(pIsBF);
            }
            if (isGive.HasValue)
            {
                command.Parameters.Add(pIsGive);
            }
            return ExecuteDataTable(command, "BatchNo Desc,Code", pageSize, currentPageIndex, out pageCount, out recordCount);
        }

        /// <summary>
        /// 更新最后批次（未输出）审核作业表的项目信息
        /// </summary>
        /// <param name="contract"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int UpdateProjectInfo(BD_Project project, DbTransaction transaction)
        {
            DbParameter pProjectID=CreateParameter("ProjectID");
            pProjectID.DbType = DbType.String;
            pProjectID.Value = project.ID;
            string sql = string.Format(@"Update A Set A.ProjectCode=B.Code,A.ProjectName=Name,A.ProjectCategory=B.ProjectCategory,
                    A.Owner971=B.Owner971,A.Owner971Name=C.CompName,A.Owner=B.Owner,A.OwnerName=D.CompName,
                    A.DOwnerName=E.CompName
                    From Bu_AuditTable A Inner Join BD_Project B On A.ProjectID=B.ID
                    Inner Join Org_Company C On B.Owner971=C.ID
                    Inner Join Org_Company D On B.Owner=D.ID
                    Left Join Org_Company E On B.DOwner=E.ID
                    Where A.ProjectID={0}
                    And A.BatchNo=(Select max(BatchNo) From Bu_AuditBatch Where BatchStatus<>'{1}')",
                        pProjectID.ParameterName, ((int)EnumItem.BatchStatus.Output).ToString());
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pProjectID);
            return ExecuteNonQuery(command, transaction);
        }

        /// <summary>
        /// 更新审核作业表的合同信息
        /// </summary>
        /// <param name="contract"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int UpdateContractInfo(BD_Contract contract, DbTransaction transaction)
        {
            DbParameter pContractCode = CreateParameter("ContractCode");
            pContractCode.DbType = DbType.String;
            pContractCode.Value = contract.Code;
            DbParameter pContractName = CreateParameter("ContractName");
            pContractName.DbType = DbType.String;
            pContractName.Value = contract.Name;
            DbParameter pContractCategory = CreateParameter("ContractCategory");
            pContractCategory.DbType = DbType.String;
            pContractCategory.Value = contract.ContractCategory;
            DbParameter pContractor = CreateParameter("Contractor");
            pContractor.DbType = DbType.String;
            pContractor.Value = contract.Contractor;
            DbParameter pBank = CreateParameter("Bank");
            pBank.DbType = DbType.String;
            pBank.Value = contract.BankName;
            DbParameter pBankAccount = CreateParameter("BankAccount");
            pBankAccount.DbType = DbType.String;
            pBankAccount.Value = contract.BankAccount;
            DbParameter pDBankCompanyName = CreateParameter("DBankCompanyName");
            pDBankCompanyName.DbType = DbType.String;
            pDBankCompanyName.Value = contract.DBankCompanyName;
            DbParameter pContractID = CreateParameter("ContractID");
            pContractID.DbType = DbType.String;
            pContractID.Value = contract.ID;
            string sql = string.Format(@"Update A Set A.ContractCode={0},A.ContractName={1},A.ContractCategory={2},A.Contractor={3},
                    A.ContractorName=B.CompName,A.Bank={4},A.BankAccount={5},A.DBankCompanyName={6}
                    From Bu_AuditTable A Inner Join Org_Company B ON A.Contractor=B.ID
                    Where A.ContractID={7} 
                    And A.BatchNo=(Select max(BatchNo) From Bu_AuditBatch Where BatchStatus<>'{8}')",
                        pContractCode.ParameterName, pContractName.ParameterName, pContractCategory.ParameterName,
                        pContractor.ParameterName, pBank.ParameterName, pBankAccount.ParameterName,pDBankCompanyName.ParameterName,
                        pContractID.ParameterName, ((int)EnumItem.BatchStatus.Output).ToString());
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pContractCode);
            command.Parameters.Add(pContractName);
            command.Parameters.Add(pContractCategory);
            command.Parameters.Add(pContractor);
            command.Parameters.Add(pBank);
            command.Parameters.Add(pBankAccount);
            command.Parameters.Add(pDBankCompanyName);
            command.Parameters.Add(pContractID);
            return ExecuteNonQuery(command, transaction);
        }

        /// <summary>
        /// 删除作业表的项目委托代建单位
        /// </summary>
        /// <param name="contractId"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int DeleteATDOwnerInfo(string contractId, DbTransaction transaction)
        { 
            DbParameter pContractID = CreateParameter("ContractID");
            pContractID.DbType = DbType.String;
            pContractID.Value = contractId;
            string sql = string.Format(@"Delete From Bu_AuditTable_DOwner Where TableID
in (Select ID From Bu_AuditTable Where ContractID={0} 
And BatchNo=(Select max(BatchNo) From Bu_AuditBatch Where BatchStatus<>'{1}'))", pContractID.ParameterName, ((int)EnumItem.BatchStatus.Output).ToString());
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pContractID);
            return ExecuteNonQuery(command, transaction);
        }

        public int InsertATDOwnerInfo(string contractId, DbTransaction transaction)
        { 
             DbParameter pContractID = CreateParameter("ContractID");
            pContractID.DbType = DbType.String;
            pContractID.Value = contractId;
            string sql=string.Format(@"Insert Into Bu_AuditTable_DOwner(ID, TableID, CompanyID, CompanyName, SortCode)
Select newid(),A.ID,B.CompanyID,B.CompanyName,B.SortCode
From Bu_AuditTable A Inner Join BD_ProjectCompany B
On A.ProjectID=B.ProjectID 
Inner Join BD_Contract_ProjectDOwner C On A.ContractID=C.ContractID 
And B.CompanyID=C.CompanyID
Where A.ID in (Select ID From Bu_AuditTable Where ContractID={0}  
And BatchNo=(Select max(BatchNo) From Bu_AuditBatch Where BatchStatus<>'{1}'))", pContractID.ParameterName, ((int)EnumItem.BatchStatus.Output).ToString());
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pContractID);
            return ExecuteNonQuery(command, transaction);
        }

        /// <summary>
        /// 根据回执ID删除作业表
        /// </summary>
        /// <param name="arrRecID"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int DeleteByRecID(string[] arrRecID, DbTransaction transaction)
        {
            string sql = @"Delete From Bu_AuditTable Where ID in
                (Select TableID From Bu_BatchRec Where RecID in ('" + string.Join("','", arrRecID) + @"'))";
            return ExecuteNonQuery(sql, transaction);
        }

        public DataTable GetMaxBatchAuditTableByProject(string[] arrProjectID)
        {
            string sql = @"Select ID From Bu_AuditTable Where 
                BatchNo=(Select max(BatchNo) From Bu_AuditBatch 
                Where BatchStatus = '" + ((int)EnumItem.BatchStatus.Active).ToString() + "') And ProjectID in ('" + string.Join("','", arrProjectID) + "') Order by Code";
            return ExecuteDataTable(sql);
        }
        /// <summary>
        /// 根据项目的到初审金额
        /// </summary>
        /// <param name="projectId"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public decimal GetAmountSByProject(string projectId,DateTime date)
        {
            DbParameter pProjectID = CreateParameter("ProjectID");
            pProjectID.DbType = DbType.String;
            pProjectID.Value = projectId;
            DbParameter pDate = CreateParameter("EndDate");
            pDate.DbType = DbType.DateTime;
            pDate.Value = date;
            string sql = string.Format(@"Select Sum(A.Amount_S) sumAmount 
                From Bu_AuditTable A
                Inner Join Bu_AuditBatch B
                On A.BatchID=B.ID
                Where A.ProjectID={0} and B.BatchStatus<>'{1}'
                And B.EndDate<={2}",
                pProjectID.ParameterName,((int)EnumItem.BatchStatus.Active).ToString(),pDate.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pProjectID);
            command.Parameters.Add(pDate);
            object obj = ExecuteScalar(command);
            if (obj == null || obj == DBNull.Value) { return 0; }
            return Convert.ToDecimal(obj);
        }

        /// <summary>
        /// 退回回执
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int BackDocReceive(string tableID, DbTransaction transaction)
        {
            DbParameter pID = CreateParameter("TableID");
            pID.DbType = DbType.String;
            pID.Value = tableID;
            DbParameter pID2 = CreateParameter("TableID");
            pID2.DbType = DbType.String;
            pID2.Value = tableID;
            int i = 0;
            string sql0 = "Update Bu_AuditTable Set Amount_DocRec=0,Amount_DocRec_S=0,Amount_S=0 where ID='" + tableID + "'";
            i = ExecuteNonQuery(sql0, transaction);
            string sql = string.Format(@"Update B Set B.Amount=0,B.DocRecieveStatus='{0}' 
                From Bu_BatchRec A Inner Join Bu_DocReceive B On A.RecID=B.ID
                Where A.TableID={1}", ((int)EnumItem.DocRecieveStatus.Back).ToString(), pID.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pID);
            i+= ExecuteNonQuery(command, transaction);
            string sql2 = string.Format(@"Update B Set B.Amount=0,B.DocRecieveStatus='{0}' 
                From Bu_BatchRec A Inner Join Bu_DocReceive_Temp B On A.RecID=B.ID
                Where A.TableID={1}", ((int)EnumItem.DocRecieveStatus.Back).ToString(), pID2.ParameterName);
            DbCommand command2 = GetDbCommand(sql2);
            command2.Parameters.Add(pID2);
            i += ExecuteNonQuery(command2, transaction);
            return i;
        }
    }
}
